import random
import pymysql
from utils.time_util import stamp_time, timestamp2str, stamp_ms

emailArray = (
    "@163.com", "@139.com", "@qq.com", "@gmail.com", "@136.com", "@sina.com", "@sohu.com", "@yahoo.com",
    "@hotmail.com")

connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='shop',
    charset='utf8'
)


def addgoodsComment():
    # 第一步
    # 添加商品评论

    cursor = connect.cursor()
    sql = 'select goods_id,sales_volume,comments_number from ecs_goods'
    cursor.execute(sql)
    goods = cursor.fetchall()

    count = 0
    # 查询是否存在商品
    if len(goods) != 0:
        with open("../file/pinglun.txt", "rb") as f:
            pinglun = f.read().decode('utf8').split()
        with open("../file/username.txt", "rb") as f:  # 打开文件
            name = f.read().decode('utf8').split("\n")

        for goodsInfo in goods:
            for info in range(0, goodsInfo[2]):
                id = goodsInfo[0]
                content = random.sample(pinglun, 1)
                user = random.sample(name, 1)
                startTime = stamp_time('2021-09-05 18:43:46') + random.randint(5000, 10000000)
                email = str(random.randint(10000000, 999999999999)) + emailArray[random.randint(0, 8)]
                rank = random.randint(2, 5)

                sql = r"INSERT INTO ecs_comment(comment_type,id_value,email,user_name,content,comment_rank,add_time,ip_address,status,parent_id,user_id) VALUES (0,'" + str(
                    id) + "','" + email + "','" + user[0] + "','" + content[0] + "','" + str(rank) + "','" + str(
                    startTime) + "','0.0.0.0',1,0,1)"
                cursor.execute(sql)
                print("当前时间：" + str(timestamp2str(startTime, '%Y-%m-%d %H:%M:%S')) + "，当前商品ID：" + str(id))


def addGoodsRecord():
    # 第二步
    # 插入购买记录

    cursor = connect.cursor()
    orderId = 'select max(order_id),max(order_sn) from ecs_order_info'
    cursor.execute(orderId)
    id = cursor.fetchall()
    if id[0][0] == None:
        orderId = 1000
    else:
        orderId = id[0][0]

    if id[0][1] == None:
        orderSn = stamp_ms()
    else:
        orderSn = id[0][1]

    sql = 'select goods_id,goods_name,goods_sn,sales_volume from ecs_goods'
    cursor.execute(sql)
    goods = cursor.fetchall()

    # 查询是否存在商品
    if len(goods) != 0:
        for goodsInfo in goods:
            for info in range(0, goodsInfo[3]):  # goodsInfo[3]为订单数
                addUserRecord(orderId, orderSn)
                sql = "INSERT INTO ecs_order_goods (order_id,goods_id,goods_name,goods_sn,market_price,goods_attr) VALUES ('" + str(
                    orderId) + "', '" + str(goodsInfo[0]) + "', '" + goodsInfo[1] + "', '" + str(
                    goodsInfo[2]) + "','42', '212')"
                cursor.execute(sql)
                orderId = orderId + 1
                orderSn = orderSn + 100
            print(goodsInfo[1] + "_完成")


def addUserRecord(orderId, order_sn):
    # 添加已购买用户信息

    userid = '1'
    order_status = '5'
    shipping_status = '2'
    pay_status = '2'
    consignee = '王先生'
    country = '1'
    province = '2'
    city = '52'
    district = '502'
    address = '中关村'
    tel = '010-2254871'
    mobile = '13328945326'
    email = '113244590@qq.com'
    shipping_id = '3'
    shipping_name = 'EMS'
    pay_id = '1'
    pay_name = '余额支付'
    how_oos = '等待所有商品备齐后再发'
    agency_id = '0'
    inv_type = ''
    tax = '0'
    discount = '0'

    cursor = connect.cursor()
    sql = "INSERT INTO ecs_order_info(order_id,order_sn,user_id,order_status,shipping_status,pay_status,consignee,country,province, " \
          "city,district,address, tel,mobile,email,shipping_id,shipping_name,pay_id,pay_name,how_oos,agency_id,inv_type,tax,discount) " \
          "VALUES ('" + str(orderId) + "','" + str(
        order_sn) + "','" + userid + "','" + order_status + "','" + shipping_status + "','" + pay_status + "','" + consignee + "'," \
                                                                                                                               "'" + country + "','" + province + "','" + city + "','" + district + "','" + address + "','" + tel + "','" + mobile + "','" + email + "','" + shipping_id + "'," \
                                                                                                                                                                                                                                                                                           "'" + shipping_name + "','" + pay_id + "','" + pay_name + "','" + how_oos + "','" + agency_id + "','" + inv_type + "','" + tax + "','" + discount + "')"
    cursor.execute(sql)


if __name__ == '__main__':
    addGoodsRecord()
